package com.yenlien.traceyun.plant.dao;

import com.alibaba.fastjson.JSONObject;
import com.yenlien.traceyun.plant.entity.Plant;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface PlantDao extends BaseJpaDao<Plant> {

    @Query(value = "SELECT COUNT(plant_number) FROM t_plant WHERE company_id = ?1", nativeQuery = true)
    String plantNumCount(String companyId);

    // 根据设备名获取对应的种植信息（前提是该设备已与地块绑定）
    @Query(value = "SELECT * FROM t_plant WHERE soil_id = (SELECT massif_id FROM t_base_soil_equipment WHERE equipment_id = ?1)", nativeQuery = true)
    Plant getByEquId(String devId);



    @Query(value = "SELECT * FROM t_plant WHERE soil_id = ?1 AND jy_name LIKE CONCAT('%', ?2, '%')", nativeQuery = true)
    List<Plant> getPlantBySoilId(String soilId, String jyName);




    @Query(value = "SELECT IFNULL(SUM(plant_number), 0) totalNum FROM t_plant WHERE DATE_FORMAT(`plant_time`, '%Y-%m') = ?1 AND company_id = ?2", nativeQuery = true)
    Float getTotalNum(String time, String companyId);

    @Query(value = "SELECT IFNULL(SUM(plant_number), 0) totalNum FROM t_plant WHERE DATE_FORMAT(`plant_time`, '%Y-%m') = ?1 AND company_id = ?2 AND jy_name = ?3", nativeQuery = true)
    String getTotalNum(String time, String companyId, String jyName);

    @Query(value = "SELECT jy_name jyName, total, num, CONCAT( round( num / total * 100.00, 3), '%') percent FROM " +
            "( SELECT * FROM ( SELECT jy_name, SUM( plant_number ) num FROM t_plant WHERE company_id = ?1 AND plant_time BETWEEN ?2 AND ?3 GROUP BY jy_name ) t1 " +
            " JOIN ( SELECT SUM( plant_number ) total FROM t_plant WHERE company_id = ?1 AND plant_time BETWEEN ?2 AND ?3) t2) t", nativeQuery = true)
    List<JSONObject> getPercent(String companyId, String startTime, String endTime);


    @Query(value = "SELECT tc.credit_code, tp.`jy_name` source_name, tp.batch `code`, IF(tp.source = '栽培', 1, 2), tp.plant_area area, DATE_FORMAT(tp.plant_time, '%Y-%m-%d') start_time, tp.way method  FROM t_plant tp LEFT JOIN " +
            "t_company tc ON tp.company_id = tc.id WHERE tc.parent_id IN (?1) AND tc.dock_province = '1'", nativeQuery = true)
    List<JSONObject> dock3(List<String> parentIdList);


    //plant_base_id 就是基地 t_base_soil 的ID
    @Query(value = "SELECT tmc.`name` source_name,  tp.batch `code`, IF(tp.source = '栽培', 1, 2) source, tp.plant_area area, DATE_FORMAT(tp.plant_time, '%Y-%m-%d') start_time, tp.way method  FROM t_plant tp " +
            " JOIN t_model_crop tmc ON tp.jy_id = tmc.id WHERE tp.company_id = ?1 AND plant_base_id = ?2", nativeQuery = true)
    List<JSONObject> dock3(String companyId, String baseSoilId);

//    @Query(value = "SELECT tp.`jy_name` source_name,  tp.batch `code`, IF(tp.source = '栽培', 1, 2) source, tp.plant_area area, DATE_FORMAT(tp.plant_time, '%Y-%m-%d') start_time, tp.way method  FROM t_plant tp " +
//            " WHERE tp.company_id = ?1 AND plant_base_id = ?2 LIMIT 1", nativeQuery = true)
//    JSONObject dock3(String companyId, String baseSoilId);

    @Query(value = "SELECT tp.`jy_name` source_name, tp.plant_base_id,  tp.batch `code`, IF(tp.source = '栽培', 1, 2) source, tp.plant_area area, DATE_FORMAT(tp.plant_time, '%Y-%m-%d') start_time, tp.way method  FROM t_plant tp " +
            " WHERE tp.company_id = ?1", nativeQuery = true)
    List<JSONObject> dock3(String companyId);

    Plant findById(String id);

    Plant findByBatch(String plantBatch);

    List<Plant> findByPlantBaseId(String plantBaseId);

    List<Plant> findBySoilId(String soilId);

    @Query(value = "select plant_name from t_plant where company_id = ?1 group by plant_name", nativeQuery = true)
    List<String> findSelect(String companyId);

    List<Plant> findByReapStatusAndSoilId(int reapStatus, String soildId);

    List<Plant> findByCompanyIdIn(List companyId);

    List<Plant> findByCompanyIdAndSoilId(String companyId, String soilId);

    List<Plant> findByCompanyId(String companyId);

    @Query(value = "SELECT plant_area FROM t_plant WHERE company_id = ?1", nativeQuery = true)
    List<Double> getAreaByCompanyId(String companyId);

    @Query(value = "select distinct plant_name from t_plant where company_id = ?1", nativeQuery = true)
    List<String> findPlantName(String companyId);


    List<Plant> findByPlantNameAndCompanyId(String p, String c);

    @Query(value = "select sum(plant_area) from t_plant where company_id=?1", nativeQuery = true)
    String plantAreaCount(String companyId);

    //种植地块数
    @Query(value = "select count(id) from t_plant where  company_id=?1 and reap_status in(0)", nativeQuery = true)
    int plantDKCount(String companyId);

    //	@Query(value = "select id,company_id,createtime,last_step,user_id,batch,company," +
//			"jy_name,place_description,plant_area,plant_base_name,plant_img," +
//			"plant_number,plant_time,plant_user_id,plant_user_name,plant_base_id,plant_name," +
//			"reap_status,reap_time,soil_id,soil_name,source,units,way,jy_id,cross_point,recall" +
//			" from t_plant where soil_id=?1", nativeQuery = true)
    @Query(value = "select * from t_plant where soil_id=?1", nativeQuery = true)
    List<Plant> findByBaseSolieId(String baseSoilId);

//	@Deprecated
//	@Query(value = "SELECT tp.id OBJ_ID, tp.batch BATCH_ID, tp.plant_time PLANT_DATE, tp.plant_user_name PRINCIPAL_NAME, tp.plant_area PROPORTION_AMOUNT, " +
//			"tc.address CITY_CODE, tc.credit_code PLANT_ID, tc.name PLANT_NAME FROM t_plant tp " +
//			"JOIN t_company tc ON tp.company_id = tc.id WHERE tp.id NOT IN(SELECT DISTINCT tp.id FROM t_plant tp JOIN dock_zhongyang dz ON tp.id = dz.plant_id " +
//			"WHERE dz.type = \"SnPlantBase\") AND tc.credit_code IS NOT NULL", nativeQuery = true)
//	List<SnPlantBaseDTO> getSnPlantBaseDTO();

    /**
     * 查询未对接的基础种植信息, 如果该种植信息绑定的公司没上传到中央平台，那么该条信息也不上传
     *
     * @return
     */
    @Query(value = "SELECT tp.* FROM t_plant tp LEFT JOIN dock_zhongyang dz ON tp.id = dz.base_plant_id JOIN t_company tc ON tp.company_id = tc.id " +
            "WHERE dz.id IS NULL AND (ISNULL(tc.credit_code) = 0 AND LENGTH(trim(tc.credit_code)) > 0) AND tc.id = \"2c9d0ded78b625970178bb8d5ac8445\"", nativeQuery = true)
    List<Plant> getBasePlant();

    @Query(value = "SELECT tp.* FROM t_plant_inputs tpi JOIN t_plant_farm_inputs tpfi ON tpi.id = tpfi.inputs_id JOIN t_plant_farm tpf ON tpfi.farm_id = tpf.id JOIN t_plant tp ON " +
            "tpf.plant_id = tp.id WHERE tpi.id = ?1", nativeQuery = true)
    List<Plant> getPlantByInputs(String inputId);

    /**
     * 根据公司 id 获取今年的种植批次总数
     *
     * @param companyId
     * @return
     */
    @Query(value = "SELECT COUNT(DISTINCT batch) FROM t_plant WHERE company_id = ?1 AND (createtime BETWEEN DATE_FORMAT(NOW(), '%Y-01-01 00:00:00') " +
            "AND DATE_FORMAT(NOW(), '%Y-12-31 23:59:59'))", nativeQuery = true)
    int getBatchNum(String companyId);

    @Query(value = "SELECT COUNT(DISTINCT jy_name) FROM t_plant WHERE company_id = ?1", nativeQuery = true)
    int getJyNum(String companyId);


}
